1) For VideoDetails, you will need two OLE DB Sources in the Data Flow tab. Start with the following OLE DB Source first.
select distinct A.CategoryId, B.Category, A.CreatedDate, B.LibraryId, A.LinkUrl, A.LastModifiedBy, A.LastModifiedDate, A.CreatedByUser, B.ItemId, A.VideoPath
from [dnnuser].[UVG_Video] A
JOIN [dnnuser].[UVG_Category] B on A.CategoryId = B.ItemId
WHERE A.VideoPath IS NOT NULL
2) Make sure to edit in the Advanced Editor for the column: Category; this column refers to the video name. Right-Click for this OLE DB Source and click on "Show Advanced Editor". Follow the following instructions to change the Output Columns for "Category" and "VideoPath". Also, create Data Conversion, Sort, and Derived Column.
For the Derived Column, you will use the following Expression: SUBSTRING([Copy of VideoPath],28,38). The following GIF will go over more details. This will remove a number of unwanted characters at the beginning and end of Copy of VideoPath so that it can be used to join (as "Derived Column 1" in the GIF notes) later for the Merge Join step.
8) Now, move onto the next OLE DB Source and connect to the Staging Table.
The second Derived Column helps add "http:" to the beginning of each string of the VideoPath column so as to complete the hyperlink; As a result, if the link were to be clicked on, the corresponding video would open in a new window.
OLE DB Destination needs to connect to dic_VideoDetails. The following are the mappings.
The different alias names of the ODS Layer